/*
 * Copyright 2000-2005 by Mark A. Kobold
 * 
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with the
 * License. You may obtain a copy of the License at http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * 
 * The Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
 * 
 * The Initial Developer of the Original Code is Markus A. Kobold.
 * 
 * Portions created by Mark A. Kobold are Copyright (C) 
 * 2000-2005 Mark A. Kobold. All Rights Reserved. 
 * 
 * Contributor(s): 
 *  Mark A. Kobold <mkobold@isqlviewer.com>.
 * 
 * Contributor(s): all the names of the contributors are added in the source
 * code where applicable.
 * 
 * If you didn't download this code from the following link, you should check
 * if you aren't using an obsolete version: http://isql.sourceforge.net/
 */
package org.isqlviewer.util;

import java.lang.management.ManagementFactory;
import java.lang.management.MemoryMXBean;
import java.lang.management.ThreadMXBean;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.Date;
import java.util.concurrent.TimeUnit;

import org.isqlviewer.JdbcCommandLogger;
import org.isqlviewer.history.CommandType;
import org.isqlviewer.history.HistoricalCommand;
import org.isqlviewer.sql.ConnectionProfile;
import org.isqlviewer.sql.JdbcService;
import org.isqlviewer.sql.JdbcUtilities;
import org.isqlviewer.sql.ResultSetRenderer;

/**
 * TODO Add QueryExecutor Overview JavaDoc.
 * <p>
 * TODO Finish this class.
 * 
 * @author Mark A. Kobold &lt;mkobold at isqlviewer dot com&gt;
 * @version 1.0
 */
public class QueryExecutor extends LoggableObject implements Runnable {

    private static final String DELIM_START = "${";
    private static final char DELIM_STOP = '}';
    private static final int DELIM_START_LEN = 2;
    private static final int DELIM_STOP_LEN = 1;
    private static final String RESOURCE_BUNDLE = "org.isqlviewer.util.ResourceBundle";
    private static final LocalMessages messages = new LocalMessages(RESOURCE_BUNDLE);

    private ResultSetRenderer renderer = null;
    private String statement = null;
    private JdbcService service = null;

    public QueryExecutor(JdbcService service, ResultSetRenderer renderer, String statement) {

        if (renderer == null) {
            this.renderer = new NullResultSetViewer();
        } else {
            this.renderer = renderer;
        }
        this.service = service;
        this.statement = statement;
    }

    public void run() {

        if (service == null) {
            return;
        }

        Connection connection = null;
        try {
            try {
                connection = service.getConnection();
                connection.clearWarnings();
            } catch (SQLException sqle) {
                renderer.handleSQLException(sqle);
                return;
            }

            String localID = Long.toHexString(System.currentTimeMillis());
            StringTokenizer st = new StringTokenizer(statement, ";", false);
            st.setQuotesEnabled(true);

            String[] statements = st.toArray();
            JdbcCommandLogger commandLogger = service.getCommandLogger();
            HistoricalCommand executionHistory = new HistoricalCommand();
            executionHistory.setCommandText("");
            executionHistory.setQueryTime(new Date());
            executionHistory.setTransactionId(System.currentTimeMillis());
            executionHistory.setService(service.getName());
            executionHistory.setType(CommandType.BATCH_QUERY);
            boolean logBatch = statements.length > 1;
            for (int i = 0; i < statements.length; i++) {
                renderer.initialize();
                String userQuery = statements[i].trim();
                if (userQuery.length() > 0) {
                    try {
                        String substitutedQuery = doVariableSubstitutions(userQuery);
                        String nativeSQL = getNativeSql(connection, substitutedQuery);
                        Date queryTime = new Date();
                        doQuery(connection, localID, nativeSQL);
                        if (logBatch && commandLogger != null) {
                            HistoricalCommand subCommand = new HistoricalCommand();
                            subCommand.setCommandText(userQuery);
                            subCommand.setQueryTime(queryTime);
                            subCommand.setService(service.getName());
                            subCommand.setTransactionId(executionHistory.getTransactionId());
                            getTypeForQuery(subCommand, userQuery);
                            executionHistory.addSubcommand(subCommand);
                        } else {
                            logCommandHistory(commandLogger, userQuery, queryTime);
                        }
                    } catch (ParseException e) {
                        error("ERR:", e);
                    } catch (SQLException sqle) {
                        Object[] arguments = new Object[3];
                        arguments[0] = sqle.getMessage();
                        arguments[1] = Integer.toString(sqle.getErrorCode());
                        arguments[2] = sqle.getSQLState();
                        error(messages.format("queryexecutor.sql_execption", arguments));
                        renderer.handleSQLException(sqle);
                    } catch (Exception e) {
                        error("ERR:", e);
                    } finally {
                        renderer.finalizeStatement(localID);
                    }
                }
            }
            if (commandLogger != null && logBatch) {
                commandLogger.logCommand(executionHistory);
            }
        } finally {
            info(messages.getMessage("queryexecutor.finished"));
            System.gc();
        }

    }

    private void logCommandHistory(JdbcCommandLogger commandLogger, String userQuery, Date queryTime) {

        if (commandLogger != null) {
            HistoricalCommand command = new HistoricalCommand();
            command.setCommandText(userQuery);
            command.setQueryTime(queryTime);
            command.setService(service.getName());
            getTypeForQuery(command, userQuery);
            commandLogger.logCommand(command);
        }
    }

    private void getTypeForQuery(HistoricalCommand command, String userQuery) {

        StringBuilder builder = new StringBuilder("");
        for (int i = 0; i < userQuery.length(); i++) {
            char c = userQuery.charAt(i);
            if (Character.isWhitespace(c)) {
                break;
            }
            builder.append(c);
        }

        String firstWord = builder.toString().toUpperCase();
        if ("UPDATE".equals(firstWord)) {
            command.setType(CommandType.UPDATE_QUERY);
        } else if ("INSERT".equalsIgnoreCase(firstWord)) {
            command.setType(CommandType.INSERT_QUERY);
        } else if ("DELETE".equalsIgnoreCase(firstWord)) {
            command.setType(CommandType.DELETE_QUERY);
        } else if ("CREATE".equalsIgnoreCase(firstWord)) {
            command.setType(CommandType.INSERT_QUERY);
        } else if ("DROP".equalsIgnoreCase(firstWord)) {
            command.setType(CommandType.DELETE_QUERY);
        } else {
            command.setType(CommandType.QUERY);
        }
    }

    private void doQuery(Connection connection, String localID, String nativeSQL) throws SQLException {

        boolean results = false;

        ConnectionProfile profile = service.getProfile();
        DatabaseMetaData jdbcMetaData = connection.getMetaData();
        Statement sqlStatement = connection.createStatement();
        renderer.statementInitialized(localID, sqlStatement);

        MemoryMXBean memoryMBean = ManagementFactory.getMemoryMXBean();
        ThreadMXBean threadMBean = ManagementFactory.getThreadMXBean();
        long cpuTime = 0;
        long vmMemory = 0;

        boolean returnKeys = false;
        boolean generateKeys = profile.isResultsetKeys();
        boolean supportsMultiResults = jdbcMetaData.supportsMultipleResultSets();
        // if escape processing is enable it now otherwise leave with driver default //
        if (!profile.isEscapeProcessing()) {
            sqlStatement.setEscapeProcessing(profile.isEscapeProcessing());
        }

        if (generateKeys) {
            if (jdbcMetaData.supportsGetGeneratedKeys()) {
                info(messages.format("queryexecutor.generated_keys_query", nativeSQL));
                cpuTime = threadMBean.getCurrentThreadUserTime();
                vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
                results = sqlStatement.execute(nativeSQL, Statement.RETURN_GENERATED_KEYS);
                vmMemory = vmMemory - memoryMBean.getHeapMemoryUsage().getUsed();
                cpuTime = threadMBean.getCurrentThreadUserTime() - cpuTime;
                returnKeys = true;
            } else {
                info(messages.format("queryexecutor.generated_keys_query_not_supported", nativeSQL));
                cpuTime = threadMBean.getCurrentThreadUserTime();
                vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
                results = sqlStatement.execute(nativeSQL, Statement.NO_GENERATED_KEYS);
                vmMemory = vmMemory - memoryMBean.getHeapMemoryUsage().getUsed();
                cpuTime = threadMBean.getCurrentThreadUserTime() - cpuTime;
            }
        } else {
            info(messages.format("queryexecutor.executing_query", nativeSQL));
            cpuTime = threadMBean.getCurrentThreadUserTime();
            vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
            results = sqlStatement.execute(nativeSQL);
            vmMemory = vmMemory - memoryMBean.getHeapMemoryUsage().getUsed();
            cpuTime = threadMBean.getCurrentThreadUserTime() - cpuTime;
        }

        String execTime = StringUtilities.getFullHumanReadableTime(TimeUnit.NANOSECONDS.toMillis(cpuTime));
        info(messages.format("queryexecutor.execution_time", execTime));
        info(messages.format("queryexecutor.execution_memory_profile", StringUtilities.getHumanReadableSize(vmMemory)));
        int rowsAffected = -1;
        if (!results) {
            rowsAffected = sqlStatement.getUpdateCount();
            NumberFormat nf = NumberFormat.getIntegerInstance();
            info(messages.format("queryexecutor.rows_affected", nf.format(rowsAffected)));
        } else {
            rowsAffected = -1;
        }

        int rsCount = 0;

        do {
            ResultSet rs = null;
            if (results) {
                rs = sqlStatement.getResultSet();
                try {
                    cpuTime = threadMBean.getCurrentThreadUserTime();
                    vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
                    long rowsProcessed = renderer.processResultSet(localID, rs, nativeSQL, rsCount);
                    vmMemory = vmMemory - memoryMBean.getHeapMemoryUsage().getUsed();
                    cpuTime = threadMBean.getCurrentThreadUserTime() - cpuTime;

                    String time = StringUtilities.getFullHumanReadableTime(TimeUnit.NANOSECONDS.toMillis(cpuTime));
                    String memory = StringUtilities.getHumanReadableSize(vmMemory);
                    info(messages.format("queryexecutor.records_processed", new Long(rowsProcessed)));
                    info(messages.format("queryexecutor.render_time", time));
                    info(messages.format("queryexecutor.render_memory_profile", memory));
                } catch (Throwable error) {
                    error(messages.format("queryexecutor.general_render_error"), error);
                }
            } else if (rowsAffected >= 0) {
                try {
                    renderer.processRowUpdates(localID, rowsAffected, nativeSQL);
                } catch (Throwable error) {
                    error(messages.format("queryexecutor.general_render_error"), error);
                }
            }

            if (!renderer.isCancelled()) {
                if (rs != null) {
                    renderer.recieveResultsetWarnings(rs, rs.getWarnings());
                }
                renderer.recieveStatementWarnings(sqlStatement, sqlStatement.getWarnings());
                if (renderer.supportsGeneratedKeys() && returnKeys) {
                    try {
                        rs = sqlStatement.getGeneratedKeys();
                        renderer.processGeneratedKeys(localID, rs, nativeSQL);
                    } catch (Throwable error) {
                        error(messages.format("queryexecutor.general_render_error", error));
                    } finally {
                        try {
                            if (rs != null) {
                                rs.close();
                            }
                        } catch (Throwable ignored) {
                        }
                    }
                }
            }

            if (!supportsMultiResults) {
                boolean autoClose = !renderer.supportsUpdateableResultSets();
                if (!JdbcUtilities.isUpdatable(rs) || autoClose) {
                    try {
                        if (rs != null) {
                            // logEvent(BasicUtilities.getString(resourceBundle, "ResultSet_Closing"));
                            rs.close();
                        }
                    } catch (SQLException sqle) {
                        sqle.printStackTrace();
                        // HandleSQLException(sqle);
                    }
                } else {
                    // signals member value for closing statement after
                    // this method.
                    // closeStatement = false;
                    // logEvent(BasicUtilities.getString(resourceBundle, "ResultSet_Closing_Skip"));
                }
                break;
            }
            results = sqlStatement.getMoreResults();
            if (rs != null) {
                if (results) {
                    try {
                        rs.close();
                    } catch (SQLException ignored) {
                    }
                }
            }

            if (!results) {
                try {
                    rowsAffected = sqlStatement.getUpdateCount();
                } catch (SQLException sqle) {
                    rowsAffected = -1;
                } catch (Throwable t) {
                    rowsAffected = -1;
                }
            } else {
                rowsAffected = -1;
            }
        } while (results || rowsAffected != -1);
    }

    private String getNativeSql(Connection connection, String query) {

        try {
            return connection.nativeSQL((query == null ? "" : query));
        } catch (SQLException ignored) {
            return (query == null ? "" : query);
        }
    }

    private String doVariableSubstitutions(final String source) throws ParseException {

        StringBuffer sbuf = new StringBuffer();
        int i = 0;
        int j, k;
        while (true) {
            j = source.indexOf(DELIM_START, i);
            if (j == -1) {
                // no more variables
                if (i == 0) { // this is a simple string
                    return source;
                }
                // add the tail string which contains no variables and return the result.
                sbuf.append(source.substring(i, source.length()));
                return sbuf.toString();
            }
            sbuf.append(source.substring(i, j));
            k = source.indexOf(DELIM_STOP, j);
            if (k == -1) {
                throw new ParseException(messages.format("StringUtilities.bad_variable_format"), j);
            }

            j += DELIM_START_LEN;
            String key = source.substring(j, k);
            // first try in System properties
            String replacement = renderer.substituteVariable(key);
            // then try props parameter
            if (replacement != null) {
                // Do variable substitution on the replacement string
                // such that we can solve "Hello ${x2}" as "Hello p1"
                // the where the properties are
                // x1=p1
                // x2=${x1}
                String recursiveReplacement = doVariableSubstitutions(replacement);
                sbuf.append(recursiveReplacement);
            } else {
                sbuf.append(DELIM_START);
                sbuf.append(key);
                sbuf.append(DELIM_STOP);
            }
            i = k + DELIM_STOP_LEN;
        }
    }

}
